Method and apparatus for handling scenarios in spreadsheet documents

ABSTRACT

Methods and systems consistent with the present invention include an improved spreadsheet program which is user-friendly, and allows the user to view all previous spreadsheet documents at one time in a tree scenario. Specifically, the user enters data into a spreadsheet of the spreadsheet program, saves each data set entered into the spreadsheet as a scenario using a name or other identifier, and then the user can view all the scenarios created at one time on screen as a tree structure. The user can then compare the different scenarios the user has created, and move between the different scenarios easily.

BACKGROUND OF THE INVENTION

[0001] 1. Field of the Invention

[0002] The present invention relates to the creation of a history ofalternative data sets or scenarios in spreadsheet documents having cellsarranged in rows and columns.

[0003] 2. Description of the Related Art

[0004] Spreadsheet software is one of the most commonly used softwaretools used nowadays, apart from word processing applications. Aspreadsheet program allows the user to input data into cells which arearranged into columns and rows, and by providing calculation tools, itenables the user to easily evaluate data which can be arranged in atable form. This evaluation can be done by calculating the values ofcells by referring to the content of other cells, or it can be done bygraphically displaying the content of the spreadsheet document, or by acombination of both.

[0005] In known spreadsheet programs, the user is provided with agraphic display of a table (the spreadsheet) having rows of individualcells. The data in the table is arranged in columns, for example, named“A” to “E”, and into rows, for example, numbered from “1” to “4”. A usercan then enter data into the individual cells, such as, for example, thenumber “6” in cell “A1”.

[0006] Apart from the graphic visualization tools provided byspreadsheet programs, probably the most important feature which suchspreadsheet programs offer to the user is the possibility of calculatingthe content of the individual cells by referring to other cells. Knownspreadsheet programs such as “StarCalc” produced by Sun Microsystems,and others, provide the possibility of entering functions or formulaeinto individual cells, which define how the content of those cells is tobe derived by referring to the content of other cells. For that purpose,usually on the graphical user interface, there is some location wherethe user can define the reference and the function or formula for theindividual cells.

[0007] In many cases it is useful to carry out a calculation representedby the spreadsheet based on different alternative starting values orparameters and to compare how the result changes in dependence on thedifferent input parameters. An example may be a proposed loan. The givenparameters are the interest rate, capital stock, available funds, andthe repayment period. For bank and customer, it is useful to modify someof these parameters and observe the influence on the other parameters inorder to optionally adjust the loan to the customer's needs.

[0008] Scenarios can thus be utilized where values of spreadsheet cellsare altered to compute and visualize various results based on differentinput data. Multiple scenarios within one spreadsheet can only bemanipulated on a one-by-one basis. The user is not able to easily alterbetween different ways of trying scenarios. Setting up a new scenario ata given point always forces the user to travel back manually, allprevious scenarios set up before. This situation worsens with every newgeneration moving away from the initial data/scenario settings. This iscumbersome and time-consuming in particular if the scenarios relate tomore complex spreadsheets.

[0009] For instance, in the above example, the user may fix the amountof money the user wants to loan. The user then changes the interestrate, and then the repayment period. The user probably may want tochange the interest rate again, but with the original value for therepayment period. However, using known spreadsheet programs, the userhas to change all scenarios, the interest rate and the value for therepayment period. In the case of more complex scenarios, the user has toalter numerous scenarios just to go back to the original situation andthen again if he wants to follow the same path once more.

[0010] It would therefore be desirable to provide a more user-friendlyhandling of scenarios for spreadsheet programs.

SUMMARY OF THE INVENTION

[0011] Methods and systems consistent with the present invention includean improved spreadsheet program which is user-friendly, and allows theuser to view all previous spreadsheet documents at one time in a treescenario. Specifically, the user enters data into a spreadsheet of thespreadsheet program, saves each data set entered into the spreadsheet asa scenario, using a name or other identifier, and then the user can viewall the scenarios created at one time on screen as a tree structure. Theuser can then compare the different scenarios the user has created, andmove between the different scenarios easily.

[0012] More particularly, methods and systems consistent with thepresent invention relate to a data processing system which processes adocument having cells arranged in rows and columns, and which includesthe steps of receiving a data set defining a content of a plurality ofcells, wherein a content of one of the plurality of cells is related toa content of at least one of another of a plurality of cells; creatingan alternative data set by receiving an input defining an alternativecontent of at least one of the plurality of cells of the data set;calculating a content of the plurality of cells referencing at least oneof the plurality of cells having alternative content to result in thealternative data set having changed data in the plurality of cells;automatically storing the changed data of the alternative data set;assigning an identifier to the alternative data set to enable access tothe alternative data set; recognizing and storing an order of creationof each alternative data set; and arranging each stored alternative dataset in a tree structure.

[0013] The user is thus able to access all previous alternative datasets or scenarios the user has set up. Using the identifier, the usercan find a specific alternative data set or scenario which the userwants to use as a basis for calculating further alternative data sets orscenarios.

[0014] The data of the spreadsheet may be entered by the user or copiedfrom different computer readable documents.

[0015] The order in which the alternative data sets or scenarios arecreated by the user, is recognized and stored, so that the user canfollow the way the user has made the alternative data sets or scenarios.The user may then move forward and backward between differentalternative data sets or scenarios, or even move between arbitrary onesof the stored alternative data sets or scenarios. Thus, the user isenabled to follow the history of the alternative data sets or scenariosthe user has created, and to easily navigate through these scenarioswhich may serve as a basis for a new alternative data set or scenario.

[0016] The history of alternative data sets or scenarios in the treestructure can be displayed on a display medium, enabling the user toselect previous alternative data sets or scenarios using a graphicaluser interface. The display medium and the graphical user interfaceinclude part of the data processing system consistent with the method ofpracticing the present invention.

[0017] The identifier which identifies an alternative data set orscenario in the history of alternative data sets or scenarios, mayinclude a name. This name may be automatically created by the program inmemory of the data processing system, and/or may be selectable orchangeable by the user.

[0018] A further implementation of methods and systems consistent withthe present invention include a data processing system for processing adocument having cells arranged in rows and columns, the computerincluding: a memory containing a program that receives a data setdefining a content of a plurality of cells, wherein a content of one ofthe plurality of cells is related to a content of at least one of aplurality of other cells; creates an alternative data set by receivingan input defining an alternative content of at least one of theplurality of cells of the data set; calculates a content of theplurality of cells referencing at least one of the plurality of cellshaving alternative content to result in the alternative data set havingchanged data in the plurality of cells; automatically saves the changeddata of the alternative data set; assigns an identifier to thealternative data set to enable access to the alternative data set;recognizes and stores an order of creation of each alternative data set;and arranges each stored alternative data set in a tree structure; and aprocessor which runs the program.

[0019] In yet another implementation of methods and systems consistentwith the present invention includes an apparatus for processing adocument comprising cells arranged in rows and columns, the apparatusincluding: means for receiving a data set defining a content of aplurality of cells, wherein a content of one of the plurality of cellsis related to a content of at least one of another of a plurality ofcells; means for creating an alternative data set by receiving an inputdefining an alternative content of at least one of the plurality ofcells of the data set; means for calculating a content of the pluralityof cells referencing at least one of the plurality of cells having thealternative content to result in the alternative data set having changeddata in the plurality of cells; means for automatically storing thechanged data of the alternative data set; means for assigning anidentifier to the alternative data set to enable access to thealternative data set; means for recognizing and storing an order ofcreation of each alternative data set; and means for arranging eachstored alternative data set in a tree structure.

[0020] In still another implementation of the methods and systemsconsistent with that of the present invention include acomputer-readable memory device encoded with a tree data structurehaving entries which are accessed by a program that processes a documentcomprising cells arranged in rows and columns, wherein the program isencoded in the memory device and is run by a processor, each entrycomprising an identifier for a data set for the cells of the document toprovide access to said data set.

[0021] It can be seen from the above embodiments or implementations ofthe present invention, that the handling of alternative data sets orscenarios is greatly facilitated for the user compared with conventionalspreadsheets.

[0022] There has thus been outlined, rather broadly, some features ofthe invention in order that the detailed description thereof thatfollows may be better understood, and in order that the presentcontribution to the art may be better appreciated. There are, of course,additional features of the invention that will be described below andwhich will form the subject matter of the claims appended hereto.

[0023] In this respect, before explaining at least one embodiment of theinvention in detail, it is to be understood that the invention is notlimited in its application to the details of construction and to thearrangements of the components set forth in the following description orillustrated in the drawings. The invention is capable of otherembodiments and of being practiced and carried out in various ways.Also, it is to be understood that the phraseology and terminologyemployed herein, as well as the abstract included below, are for thepurpose of description and should not be regarded as limiting.

[0024] As such, those skilled in the art will appreciate that theconception upon which this disclosure is based may readily be utilizedas a basis for the designing of other structures, methods and systemsfor carrying out the several purposes of the present invention. It isimportant, therefore, that the claims be regarded as including suchequivalent constructions insofar as they do not depart from the spiritand scope of the present invention.

BRIEF DESCRIPTION OF THE DRAWINGS

[0025]FIG. 1 depicts a data processing system suitable for practicingmethods and systems consistent with the present invention.

[0026]FIG. 2 depicts a client-server configuration suitable forpracticing methods and systems consistent with the present invention.

[0027]FIG. 3 depicts in greater detail, the data structure depicted inFIG. 1.

[0028]FIG. 4 depicts an exemplary spreadsheet displayed by the programdepicted in FIG. 1.

[0029]FIG. 5 depicts a flowchart of the steps performed by the programdepicted in FIG. 1.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0030] Methods and systems consistent with the present invention includean improved spreadsheet program which is user-friendly, and allows theuser to view all previous spreadsheet documents at one time in a treescenario (see FIG. 3). Specifically, the user enters data into aspreadsheet of the spreadsheet program, saves each data set entered intothe spreadsheet as a scenario using a name or other identifier, and thenthe user can view all the scenarios created at one time on screen as atree structure. The user can then compare the different scenarios theuser has created, and move between the different scenarios easily.

[0031] An embodiment consistent with that of the present invention isnow discussed with reference to FIG. 1, which depicts a data processingsystem 30 for practicing methods and systems consistent with the presentinvention. The data processing system 30 may comprise a display 25 andsystem chassis 20 housing a central processing unit CPU 26, aninput/output I/O unit 21, a memory 22 with spreadsheet program 29(hereafter, “the program 29”), and a secondary storage device 24. Theprogram 29 is run from memory 22 in the computer system 30, as shown inFIG. 1, and as described below. Further, the program 29 includes a datastructure 40 which has entries that are the stored identifiers or nameswhich enable access to the scenarios presented in the tree structure.

[0032] The computer may further comprise standard input devices as inputmeans, like a keyboard 23, a mouse 28, or a speech processing means (notshown).

[0033] Although aspects of one implementation are depicted as beingstored in memory, one skilled in the art will appreciate that all orpart of systems and methods consistent with the present invention may bestored on or read from other computer-readable media, such as secondarystorage devices, like hard disks, floppy disks, and CD-ROM; a carrierwave received from a network such as the Internet; or other forms of ROMor RAM either currently known or later developed. Further, althoughspecific components of data processing system have been described, oneskilled in the art will appreciate that a data processing systemsuitable for use with methods and systems consistent with the presentinvention may contain additional or different components.

[0034] One skilled in the art will also appreciate that methods,systems, and articles of manufacture consistent with the presentinvention, may also be implemented in a client-server environment, likethe one depicted in FIG. 2. FIG. 2 depicts a client computer system 60and server computer system 50 each connected to a network, such as aLocal Area Network, Wide Area Network, or the Internet. The spreadsheetmay be displayed on a display screen of the client device 60 while someor all steps of the method and system consistent with the presentinvention, are carried out on the server computer system 50 accessibleby the client computer system 60 over, for example, the Internet, usinga browser application or the like.

[0035] Methods and systems consistent with the present invention willnow be explained in more detail, as shown in FIGS. 3-5.

[0036]FIG. 4 depicts a spreadsheet displayed by the program 29, thespreadsheet comprising cells in rows 1, 2, 3, . . . and columns A, B, Cetc. For the sake of simplicity, in FIG. 4, the formulas defining thereferences, are shown directly in the individual cells.

[0037] In method step S11, as shown in FIG. 5, the cells of thespreadsheet are filled with content, e.g. by manual input by the userand received by the program 29. It is, however, also possible to inputthe data by copying them from another spreadsheet or a data base or thelike, and the program 29 saves the inputted information.

[0038] After having input the content into the cells, the resultingcontent of the so-defined cells (in the case of FIG. 4, cells C1, D1,and E1) is automatically calculated by the program 29 by referring tothe content of the referenced cells.

[0039] In the example shown in FIG. 4, the cells A1 and B1 comprise theparameter values 6 and 10, respectively. The content of the cells C, Dand E are calculated on the basis of the parameter values A1 and B1. Inthe present example, C1 has the value 3, D1 has the value 30, and E1 hasthe value 33.

[0040] In method step S12, the program 29 detects whether or not theuser intends to create a scenario through user input via a pull-downscenario or the like. Thus, if the user wishes to know the end result E1based on a different set of parameter values or an alternative data setA, B, the user creates a scenario, for example by replacing value A1=6with A1=7. Then, if the user then wants to see the result for thecombination of parameters A=6 and B=12, the values of both cells A1 andB1 have to be changed.

[0041] Accordingly, in step S13, if the program 29 detects by thechanged parameter values being inputted by the user as an alternativedata set (as described above), that the user wishes to create ascenario, the program 29 receives and saves the changed parametervalues.

[0042] Then, in method step S14, the program 29 performs calculationsrepresented by the spreadsheet, using known techniques, based on thechanged parameter values, and the result is displayed by the program 29on a display medium 25.

[0043] Subsequently, in step S15, the scenario is stored in a storagemedium 22 or 24 by the program 29. In order to save storage space it is,of course, possible only to store those values of the data set of thescenario which have been changed with respect to an earlier data setwhich is already stored in memory 22 or 24 by the program 29.

[0044] In step S16, the program 29 automatically assigns a set name oridentifier to the scenario created by the user, and the scenario is thenstored together with the identifier in a storage medium 22 or 24 in atree structure (see FIG. 3). Alternatively, the name or identifier isassigned to a scenario by the user and received by the program 29. Theidentifier allows a later user access to the scenario by reference tothe identifier when shown in the tree structure. It is possible that theuser can change this assigned identifier or name to a more descriptivename the user can easily recognize, which is then saved in memory 22 or24 by the program 29. Thus, navigation between different scenarios of ahistory of scenarios as shown in the tree structure, is facilitated.

[0045] It should be acknowledged here that the order of execution ofmethod steps S15 and S16 may be interchanged.

[0046] In method step S17, the history of scenarios created by the useris retrieved by the program 29, and displayed by the program 29 on adisplay screen 25 as a tree structure. The scenarios can also bedisplayed as a list instead of the tree structure shown in FIG. 3. Anyother suitable type of display is also possible.

[0047] In method step S18, the user can move forward and backward orjump between the earlier scenarios already stored in the memory 22 or 24by the program 29 and displayed in the tree structure. Preferably theuser is enabled by the program 29, to move along the branches of thetree structure using an input device like a mouse 28 or jumping betweenthe different scenarios of the tree structure with the help of aparticular navigation tool.

[0048] In method step S19, if the user decides to change a scenario, andselects one of the earlier scenarios, the program 29 retrieves theselected scenario from the tree structure and displays the earlierscenario on the display screen 25 as a spreadsheet.

[0049] In step S20, based on this retrieved scenario, the user maydecide to change the scenario, and then go back to step S13 to changeone of the parameters in the spreadsheet again and create a new scenario(method steps S13-S16). Instead of manually inputting all parametervalues again the user can select the closest scenario from the scenariolist or tree structure, and take this as the starting point of a newscenario.

[0050] If the user decides not to change the scenario in step S20, thenthe user proceeds to the end of the document in step S21. Then, the usercan then either end the application, or return to step S12 to create anew scenario.

[0051] Accordingly, the use and application of scenarios, in particularto complex calculations, is greatly improved.

[0052]FIG. 3 shows an example of a tree structure data structurerepresenting a history of scenarios, the names of which are in thisexample, scenario 1, scenario 2 . . . Below the name in brackets, theparameter values on which the scenario is based, are indicated.

[0053] In the example shown in FIG. 3, i means the interest rate of aloan, r the repayment period and c the capital sum. Two scenarios areconnected by straight lines if only one parameter has been changed,i.e., c=60,000 instead of c=50,000 in scenario 2 with respect toscenario 1, or r=11 compared to r=10 in scenario 6 with respect toscenario 3. Two scenarios are connected by a broken line if they differin two parameters, as for example scenario 4 and scenario 5. The treestructure thus allows the user an easy navigation through the scenarios.

[0054] If only two parameters are changed in the scenarios, arepresentation as a two dimensional table by the program 29, may beadvantageous. Changes of one parameter are represented by a position inthe horizontal direction by the program 29, and changes of the otherparameter are represented by a position in the vertical direction by theprogram 29. In the case of changing three parameters, a threedimensional representation depicted by the program 29 would bedesirable.

[0055] It should be appreciated by one skilled in the art that theprogram 29 comprises means for receiving the inputted data set, meansfor creating an alternative data set by receiving the input defining analternative content of the data set, means for calculating a content ofthe cells referencing the alternative content to result in thealternative data set having changed data, means for automaticallystoring the changed data of the alternative data set, means forassigning an identifier to the alternative data set to enable access tothe alternative data set, means for recognizing and storing an order ofcreation of each alternative data set, and means for arranging eachstored alternative data set in a tree structure.

[0056] While the invention has been particularly shown with reference tothe above embodiments, it will be understood by those skilled in the artthat various other changes in the form and details may be made thereinwithout departing from the spirit and the scope of the invention.

What is claimed is:
 1. A method in a data-processing system for processing a document comprising cells arranged in rows and columns, said method comprising the steps of: receiving a data set defining a content of a plurality of cells, wherein a content of one of said plurality of cells is related to a content of at least one of another of a plurality of cells; creating an alternative data set by receiving an input defining an alternative content of said at least one of said plurality of cells of said data set; calculating a content of said plurality of cells referencing said at least one of said plurality of cells having said alternative content to result in said alternative data set having changed data in said plurality of cells; automatically storing said changed data of said alternative data set; assigning an identifier to said alternative data set to enable access to said alternative data set; identifying and storing an order of creation of each said alternative data set; and arranging each said stored alternative data set in a tree structure.
 2. The method of claim 1, wherein the document is a spreadsheet document.
 3. The method of claim 1, wherein said data set is inputted by a user.
 4. The method of claim 1, wherein said stored alternative data set is transmitted from a computer memory device.
 5. The method of claim 1, further comprising the step of moving between each said different stored alternative data set.
 6. The method of claim 1, wherein said identifier allows access to an arbitrary one of each said stored alternative data set.
 7. The method of claim 1, further comprising the steps of: displaying said tree structure on a display medium; and enabling the user to select said alternative data set represented in said tree structure using a graphical user interface.
 8. The method of claim 1, wherein said identifier of said alternative data set comprises a name.
 9. The method of claim 8, wherein said name is automatically created when said alternative data set is stored.
 10. The method of claim 8, wherein said name is selectable by the user.
 11. The method of claim 6, further comprising: providing the user a navigation tool to move between each said different alternative data set.
 12. A computer readable medium containing instructions that cause a data processing system to perform a method of processing a document comprising cells arranged in rows and columns, the method comprising the steps of: receiving a first data set in at least one of a plurality of cells; creating an alternative data set by receiving input into said plurality of cells and automatically performing a predetermined calculation based on said first data set; automatically storing said alternative data set; assigning an identifier to said alternative data set; and storing said data set and said alternative data set in a tree structure.
 13. The method of claim 12, wherein said first data set in said at least one of said plurality of cells is derived based on a relationship to a content in at least another of said plurality of cells.
 14. The method of claim 12, wherein said identifier allows access to said alternative data set.
 15. The method of claim 12, wherein each said alternative data set is stored in an order of creation.
 16. The method of claim 12, wherein the document is a spreadsheet document.
 17. The method of claim 12, wherein said data set is inputted by a user.
 18. The method of claim 12, wherein each said stored alternative data set is transmitted from a computer memory device.
 19. The method of claim 12, further comprising the step of moving between each said different stored alternative data set.
 20. The method of claim 12, wherein said identifier allows access to an arbitrary one of each said stored alternative data set.
 21. The method of claim 12, further comprising the steps of: displaying said tree structure on a display medium; and enabling the user to select each said alternative data set represented in said tree structure using a graphical user interface.
 22. The method of claim 12, wherein said identifier of each said alternative data set comprises a name.
 23. The method of claim 22, wherein said name is automatically created when each said alternative data set is stored.
 24. The method of claim 23, wherein said name is selectable by the user.
 25. The method of claim 19, further comprising: providing the user a navigation tool to move between each said different alternative data set.
 26. A data processing system for processing a document comprising cells arranged in rows and columns, said system comprising: a memory containing a program that receives a data set defining a content of a plurality of cells, wherein a content of one of the plurality of cells is related to a content of at least one of a plurality of other cells; that creates an alternative data set by receiving an input defining an alternative content of at least one of the plurality of cells of the data set; that calculates a content of the plurality of cells referencing at least one of the plurality of cells having alternative content to result in the alternative data set having changed data in the plurality of cells; that automatically saves the changed data of the alternative data set; that assigns an identifier to the alternative data set to enable access to the alternative data set; that identifies and stores an order of creation of each alternative data set; and that arranges each stored alternative data set in a tree structure; and a processor which runs the program.
 27. A computer readable medium containing instructions that cause a data processing system to perform a method of processing a document comprising cells arranged in rows and columns, the method comprising the steps of: receiving a data set defining a content of a plurality of cells, wherein a content of one of said plurality of cells is related to a content of at least one of another of a plurality of cells; creating an alternative data set by receiving an input defining an alternative content of said at least one of said plurality of cells of said data set; calculating a content of said plurality of cells referencing said at least one of said plurality of cells having said alternative content to result in said alternative data set having changed data in said plurality of cells; automatically storing said changed data of said alternative data set; assigning an identifier to said alternative data set to enable access to said alternative data set; recognizing and storing an order of creation of each said alternative data set; and arranging each said stored alternative data set in a tree structure.
 28. An apparatus for processing a document comprising cells arranged in rows and columns, said apparatus comprising: means for receiving a data set defining a content of a plurality of cells, wherein a content of one of said plurality of cells is related to a content of at least one of another of a plurality of cells; means for creating an alternative data set by receiving an input defining an alternative content of said at least one of said plurality of cells of said data set; means for calculating a content of said plurality of cells referencing said at least one of said plurality of cells having said alternative content to result in said alternative data set having changed data in said plurality of cells; means for automatically storing said changed data of said alternative data set; means for assigning an identifier to said alternative data set to enable access to said alternative data set; means for recognizing and storing an order of creation of each said alternative data set; and means for arranging each said stored alternative data set in a tree structure.
 29. A computer-readable memory device encoded with a tree data structure having entries which are accessed by a program that processes a document comprising cells arranged in rows and columns, wherein the program is encoded in the memory device and is run by a processor, each entry comprising: an identifier for a data set for the cells of the document to provide access to the data set.
 30. A method in a data processing system for processing a document having cells, the method comprising the steps of: receiving a first data set for the cells; receiving a second data set for the cells; and displaying both the first data set and the second data set at substantially the same time.
 31. The method of claim 29, wherein the data set is displayed in a tree structure.
 32. The method of claim 29, wherein said first data set and said second data set each has values, and wherein the method further comprises the step of receiving an edit to one of said values. 